﻿using System;
using System.Data;
using System.Configuration;

using nsDb;
using System.Collections.Specialized;
using System.Collections;

namespace DbControllers
{
	/// <summary>
	/// This class provides the methods for accessing the Database namespace as well stores the 
	/// parameter and result dataset in the memory
	/// </summary>
	public class TableControl
	{
		#region Member Variables
		#endregion


		#region Constructors
		/// <summary>
		/// The + instructor - creates the both datasets
		/// </summary>
		private string _ConnectionString;
		public string ConnectionString
		{
			get { return _ConnectionString; }
			set { _ConnectionString = value; }
		} //eof property FieldName 

		public TableControl ( string connectionStr )
		{
			this.ConnectionString = connectionStr;
		} //eof constructor 
		#endregion


		#region Methods
		/// <summary>
		/// Provides access to the db namespace for retrieving table metadata
		/// </summary>
		/// <param name="global.msg">the message to present to the user</param>
		/// <param name="ds">the parameter dataset</param>
		/// <param name="tableName"> the name of the table </param>
		/// <returns>true or false on success or failure </returns>
		public bool GetTableMetaData ( ref string msg, ref DataSet ds, ref string tableName, int visible )
		{
			MetaDataBaseControl mdbco = new nsDb.MetaDataBaseControl ( _ConnectionString );
			return mdbco.GetTableMetaData ( ref msg, ref ds, ref tableName, visible );
		}

        
		//callMe 	LookUpColValueById ( ref  msg,  ref  colMetaId , ref  colId , ref  colMetaValue , ref  colValue , 	ref  tableName)
		public bool LookUpColValueById ( ref string msg, ref string colMetaId, ref string colId, ref string colMetaValue,
			ref string colValue, ref string tableName )
		{
			DatabaseControl dtbc = new nsDb.DatabaseControl ( _ConnectionString );
			return dtbc.LookUpColValueById ( ref  msg, ref  colMetaId, ref  colId, ref  colMetaValue, ref  colValue, ref  tableName );
		} //eof LookUpColValueById


		//callMe 	LookUpColValueById ( ref  msg,  ref  colMetaId , ref  colId , ref  colMetaValue , ref  colValue , 	ref  tableName)
		public bool LookUpColIdByValue ( ref string msg, ref string colMetaId, ref string colId, ref string colMetaValue,
			ref string colValue, ref string tableName )
		{
			DatabaseControl dtbc = new nsDb.DatabaseControl ( _ConnectionString );
			return dtbc.LookUpColIdByValue ( ref  msg, ref  colMetaId, ref  colId, ref  colMetaValue, ref  colValue, ref  tableName );
		} //eof LookUpColValueById



		//callMe ListColValuesForLookUp ( ref msg, ref ds, ref colMetaValue, ref tableName )
		public bool ListColValuesForLookUp ( ref string msg, ref DataSet ds, ref string colMetaValue, ref string tableName )
		{
			DatabaseControl dtbc = new nsDb.DatabaseControl ( _ConnectionString );
			return dtbc.ListColValuesForLookUp ( ref msg, ref ds, ref colMetaValue, ref tableName );

		} //eof method ListColValuesForLookUp

		//Meta_Data_Get ( ref msg, ref ds, ref dbObjectName , ref visible , ref tableName )
		public bool Meta_Data_Get ( ref string msg, ref DataSet ds, ref string dbObjectName, int visible, ref string tableName )
		{
			ProcedureControl pco = new nsDb.ProcedureControl ( _ConnectionString );
			return pco.Meta_Data_Get ( ref msg, ref  ds, ref dbObjectName, visible, ref tableName );
		} //eof method  Meta_Data_Get


		public bool ListMetaDataColumn ( ref string msg, ref string typeOfObject, ref DataSet ds )
		{
			MetaDataBaseControl mdbco = new nsDb.MetaDataBaseControl ( _ConnectionString );
			return mdbco.ListMetaDataColumn ( ref msg, ref typeOfObject, ref ds );
		} //eof method  typeOfObject

		//callMe        .GetItemById ( ref msg,  ref domainName, ref rds, ref tableName , ref itemId , ref itemMetaId)
		public bool GetItemById ( ref string msg, ref string domainName, ref DataSet rds, ref string tableName,
				ref string itemId, ref string itemMetaId )
		{
			MetaDataBaseControl mdbco = new nsDb.MetaDataBaseControl ( _ConnectionString );
			return mdbco.GetItemById ( ref msg, ref domainName, ref rds, ref tableName, ref itemId, ref itemMetaId );
		} //eof method  


		public bool GetItemByIdFromProcedure ( ref string msg, ref string domainName, ref DataSet rds,
																						ref string procedureName, ref string itemId, ref string itemMetaId )
		{
			ProcedureControl pco = new nsDb.ProcedureControl ( _ConnectionString );
			return pco.GetItemByIdFromProcedure ( ref msg, ref domainName, ref rds,
																		 ref procedureName, ref itemId, ref itemMetaId );
		} //eof method GetItemByIdFromProcedure



		
        //GetTableNameColumnName(ref msg, ref ds , tableName )
        public bool GetTableNameColumnName(ref string msg, ref DataSet ds, string tableName)

        {
            MetaDataBaseControl mdbco = new nsDb.MetaDataBaseControl(_ConnectionString);
            return mdbco.GetTableNameColumnName( ref msg, ref ds, tableName);

        } //eof method 




		public bool UpdateTable ( ref string domainName, ref DataSet ds, ref string msg, ref string tableName, ref string procedureName )
		{
			ProcedureControl pco = new nsDb.ProcedureControl ( _ConnectionString );
			return pco.UpdateTable ( ref domainName, ref  ds, ref  msg, ref tableName, ref procedureName );
		} //eof method UpdateTable 



		//RunProcedureWithParamsGetDs ( ref domainName, ref msg,   ref dt, ref rds , ref procedureName )
		public bool RunProcedureWithParamsGetDs ( ref string domainName, ref string msg,
			ref DataTable dt, ref DataSet rds, string procedureName )
		{
			ProcedureControl pco = new nsDb.ProcedureControl ( _ConnectionString );
			return pco.RunProcedureWithParamsGetDs ( ref domainName, ref msg,
							ref  dt, ref rds, procedureName );
		} //eof method 



		//AddUniqueRow ( ref domainName, ref ds, ref msg,ref rds, ref tableName, ref  procedureName , ref itemId)
		public bool AddUniqueRow ( ref string domainName, ref DataSet ds, ref string msg, ref string tableName, ref  string procedureName, ref string itemId, ref string itemUniqueMetaId )
		{
			ProcedureControl pco = new nsDb.ProcedureControl ( _ConnectionString );
			return pco.AddUniqueRow ( ref domainName, ref ds, ref msg, ref tableName, ref  procedureName, ref itemId, ref itemUniqueMetaId );
		} //eof public bool Search (ref DataSet ds , ..



	//callMe AddNewRowToTable (domainName, ref ds, ref msg, tableName , procedureName )
    public bool AddNewRowToTable (string domainName, ref DataSet ds, ref string msg, string tableName , string procedureName )
    {
		    ProcedureControl pco = new nsDb.ProcedureControl ( _ConnectionString );
			return pco.AddNewRowToTable (domainName, ref ds, ref msg, tableName , procedureName );
	} //eof method AddNewRowToTable




		public bool ListColumnValues ( ref string msg, ref System.Data.DataSet des, ref string columnName, ref string tableName,
		ref string domainName, int formType )
		{
			ProcedureControl pco = new nsDb.ProcedureControl ( _ConnectionString );
			return pco.ListColumnValues ( ref  msg, ref des, ref columnName, ref tableName, ref  domainName, formType );
		} //eof public bool Search (ref DataSet ds , ..

		//former ListReports( ref msg, ref ds)
    //RunProcGetDs ( ref msg, domainName, ref procedureName, ref ds )
		public bool RunProcGetDs ( ref string msg, string domainName, string procedureName, ref DataSet ds )
		{
			ProcedureControl pco = new nsDb.ProcedureControl ( _ConnectionString );
			return pco.RunProcGetDs ( ref msg, domainName, procedureName, ref ds );

		} //eof method 



                //RunProcGetDsPassOneParam(ref msg, domainName, procedureName, ref ds , paramValue , paramMetaValue)
        public bool RunProcGetDsPassOneParam(ref string msg, string domainName, string procedureName, ref DataSet ds, string paramValue, string paramMetaValue)
        {

            ProcedureControl pco = new nsDb.ProcedureControl(_ConnectionString);
            return pco.RunProcGetDsPassOneParam(ref msg, domainName, procedureName, ref ds, paramValue, paramMetaValue);
        
        } //eof method





		//public bool GetAllFromTableOrView ( ref msg , ref domainName , ref rds , ref tableName  , ref orderByItem)
		public bool GetAllFromTableOrViewOrderBy ( ref string msg, ref string domainName, ref DataSet rds,
																		ref string tableName, ref string orderByItem )
		{
			MetaDataBaseControl mdbco = new nsDb.MetaDataBaseControl ( _ConnectionString );
			return mdbco.GetAllFromTableOrViewOrderBy ( ref msg, ref domainName, ref rds, ref tableName, ref orderByItem );
		} //eof method 



		//GetAllFromTableOrViewWhere ( ref msg, ref domainName, ref rds, ref tableName , ref ldic)
		public bool GetAllFromTableOrViewWhere ( ref string msg, ref string domainName, ref DataSet rds, ref string tableName, ref ListDictionary ldic )
		{
			MetaDataBaseControl mdbco = new nsDb.MetaDataBaseControl ( _ConnectionString );
			return mdbco.GetAllFromTableOrViewWhere ( ref msg, ref domainName, ref rds, ref tableName, ref ldic );
		
		
		} //eof method GetAllFromTableOrViewWhere





		//public bool GetAllFromTableOrView ( ref msg , ref domainName , ref rds , ref tableName  )
		public bool GetAllFromTableOrView ( ref string msg, ref string domainName, ref DataSet rds,
										ref string tableName )
		{
			MetaDataBaseControl mdbco = new nsDb.MetaDataBaseControl ( _ConnectionString );
			return mdbco.GetAllFromTableOrView ( ref msg, ref domainName, ref rds, ref tableName );
		} //eof method 

		//callMe RunProcedurePassId ( ref msg , ref procedureName , ref itemMetaId , ref itemId)
		public bool RunProcedurePassId ( ref string msg, string domainName , ref string procedureName, ref string itemMetaId, ref string itemId )
		{
			//debug Utils.Debugger.WriteIf ( "RunProcedurePassId from TableControl START" );
			ProcedureControl pco = new nsDb.ProcedureControl ( _ConnectionString );
			return pco.RunProcedurePassId ( ref msg, domainName , ref procedureName, ref itemMetaId, ref itemId );

		} //eof public bool RunProcedurePassId ( ref msg , ref procedureName , ref itemMetaId , ref itemId)


        

        //call RunSql(ref msg, domainName, ref procedureName ,ref sqlCommand , ref ds )
        public bool RunSql(ref string msg, string domainName, ref string procedureName,
            ref string sqlCommand, ref DataSet ds)
        {
            ProcedureControl pco = new nsDb.ProcedureControl(_ConnectionString);
            return pco.RunSql(ref msg, domainName, ref procedureName, ref sqlCommand, ref ds);
        } //eof method RunSql



    

public bool GetProcMeta(ref string msg , string domainName , string proc , ref DataSet ds )
{

    string paramMeta = "procName";
    string procToRun = "proc_GetProcContentByName";
    string paramValue = proc;
    DataSet dsProcText = new DataSet();        //this is the ds holding the text of the procedure 
    nsDb.MetaDataBaseControl mdbc = new MetaDataBaseControl(_ConnectionString);
    //debug Utils.Debugger.WriteIf("the connection string is " + _ConnectionString);


    

    //both the proc text retrieval and the proc parameter meta retrieval have to succeed !!!!
    if (this.RunProcGetDsPassOneParam(ref msg, domainName, procToRun, 
        ref dsProcText, paramValue, paramMeta) &&
        mdbc.GetProcedureMetaData ( ref msg , ref ds , proc ) )
    {

      //degug Utils.Debugger.DebugDataSet("from GetProcMeta --- dsProcText", ref dsProcText);
      Utils.Data.DataSetGastArbeiter.CheckForUnfilledColumns(ref msg, ref ds);
      //debug Utils.Debugger.DebugDataSet("from GetProcMeta --- ds ", ref ds);
      
      #region AddDescriptions

      int i = 0;
      DataTable dtProcText = dsProcText.Tables[0];

      foreach (DataRow drProcText in dtProcText.Rows)
      {
        string Line = drProcText[0].ToString().Trim();
        // we are looking for parameters and special comments before "AS"
        if (Line.ToLower().StartsWith("create"))
          continue;
        if (Line.ToLower().StartsWith("as"))
          continue;
        if (Line.Length == 0)
          continue;
        if (Line[0] == '@')
        {


          
          //ds.Tables[0].Rows.Add(dr);
          int j = Line.IndexOf("--");

          
          if (j >= 0)
          {
            //here are the comments per row
            ds.Tables[0].Rows[i]["DESCRIPTION"] = Line.Substring(j + 2, Line.Length - j - 2);
            Utils.Debugger.WriteIf("the description is " + ds.Tables[0].Rows[i]["DESCRIPTION"]);
            Utils.Debugger.WriteIf("The current column in ds is " + ds.Tables[0].Rows[i]["COLUMN_NAME"]);
            Line = Line.Substring(0, j);
          }




          j = Line.IndexOf("=");

          if (j >= 0)
          {
            // we have a default value to display
            string Default = Line.Substring(j + 1).Trim();
            Line = Line.Substring(0, j);
            if (Default[Default.Length - 1] == ',')
              Default = Default.Substring(0, Default.Length - 1);
            if (Default[0] == '\'')
              Default = Default.Substring(1);

            if (Default[Default.Length - 1] == '\'')
              Default = Default.Substring(0, Default.Length - 1);
            Default = Default.Replace("''", "'");
            Utils.Debugger.WriteIf("The default is " + Default);
            //h["default"] = Default;
          } //eof if 


          // get rid of any extra spaces
          Line = Line.Replace("  ", " ");
          Line = Line.Replace("  ", " ");
          Line = Line.Replace("  ", " ");
          Line = Line.Replace("  ", " ");
          string[] Parts = Line.Split(new char[] { ' ' });
          //h["name"] = Parts[0];


          ds.Tables[0].Rows[i ]["COLUMN_NAME"] =
              Convert.ToString(Parts[0]).Trim().TrimStart(new char[] { '@' });

          string dataTypeAndMaxLength = Convert.ToString(Parts[1]).Trim();
          if (dataTypeAndMaxLength.Contains("("))
          {
            string[] p = dataTypeAndMaxLength.Split(new char[] { '(' });

            ds.Tables[0].Rows[i ]["DATA_TYPE"] = Convert.ToString(p[0]).Trim();


            Utils.Debugger.WriteIf("p 1 is " + p[1].ToString());

            string maxLength = Convert.ToString(p[1]).Trim().TrimEnd(new char[] { ',' });
            Utils.Debugger.WriteIf("maxLength is " + maxLength);
            maxLength = Convert.ToString(maxLength).Trim().TrimEnd(new char[] { ')' });

            Utils.Debugger.WriteIf("maxLength is " + maxLength);
            ds.Tables[0].Rows[i]["MAX_LENGTH"] = maxLength;
          }
          else
          {
            ds.Tables[0].Rows[i]["DATA_TYPE"] = Convert.ToString(Parts[1]).Trim();
          }
          
          i++;  //increase count for parameters
          continue;
        } //eof if ( Line[0] == '@
      } //eof foreach drProcText in dt.Rows


      #endregion AddDescription 
      
      return true;

    } //eof if
    else
    {
      Utils.Debugger.WriteIf("CHECK --- GetProcMeta -> RunProcGetDsPassOneParam -- return false ");
      return false;

    } //eof else 
} //eof method GetProcMeta


public bool GetProcMetaData(ref string msg, string domainName, string procToGetMetaFor, ref DataSet ds, string procToShow, string paramMetaValue)
    {

        
        string itemMetaId = "procName";
        string procToRun = "proc_GetProcContentByName" ;
        string paramValue = procToGetMetaFor ;



        DataSet dsProcText = new DataSet();

        if (!this.RunProcGetDsPassOneParam(ref msg, domainName, procToRun, ref dsProcText, paramValue, itemMetaId))
        {
                        
            msg = "RunProcGetDsPassOneParam failed";
            return false; 
        
        } //eof if
        
        Utils.Data.DataSetGastArbeiter.CheckForUnfilledColumns(ref msg , ref ds );
        DataTable dtProcText = dsProcText.Tables[0];
    

        if (dtProcText == null)
        {
            msg = "emty datatable received ";
            return false;
        } //eof if
            
        
        int i = 0;
        DataTable dt = ds.Tables [ 0 ] ;
        foreach (DataRow drProcText in dtProcText.Rows)
        {
            string Line = drProcText[0].ToString().Trim();
            // we are looking for parameters and special comments before "AS"
            if (Line.ToLower().StartsWith("create"))
                continue;
            if (Line.ToLower().StartsWith("as"))
                continue;
            if (Line.Length == 0)
                continue;
            if (Line[0] == '@')
            {
                
                // we have a parameter
                i++;
                DataRow dr = dt.NewRow();
                ds.Tables[0].Rows.Add(dr);
                int j = Line.IndexOf("--");
                
                
                if (j >= 0)
                {
                    //here are the comments per row
                    dr["DESCRIPTION"] = Line.Substring(j + 2, Line.Length - j - 2);
                    Utils.Debugger.WriteIf("the description is " + (string)dr["DESCRIPTION"]);
                    Line = Line.Substring(0, j);
                }
                
                
                
                
                j = Line.IndexOf("=");
                
                if (j >= 0)
                {
                    // we have a default value to display
                    string Default = Line.Substring(j + 1).Trim();
                    Line = Line.Substring(0, j);
                    if (Default[Default.Length - 1] == ',')
                        Default = Default.Substring(0, Default.Length - 1);
                    if (Default[0] == '\'')
                        Default = Default.Substring(1);

                    if (Default[Default.Length-1] == '\'')
                        Default = Default.Substring(0, Default.Length - 1);
                    Default = Default.Replace("''", "'");
                    Utils.Debugger.WriteIf("The default is " + Default);
                    //h["default"] = Default;
                } //eof if 


                // get rid of any extra spaces
                Line = Line.Replace("  ", " ");
                Line = Line.Replace("  ", " ");
                Line = Line.Replace("  ", " ");
                Line = Line.Replace("  ", " ");
                string[] Parts = Line.Split(new char[] { ' ' });
                //h["name"] = Parts[0];


                ds.Tables[0].Rows[i - 1]["COLUMN_NAME"] = 
                    Convert.ToString(Parts[0]).Trim().TrimStart(new char[] { '@' });
                
                string dataTypeAndMaxLength = Convert.ToString(Parts[1]).Trim();
                if (dataTypeAndMaxLength.Contains("("))
                {
                    string[] p = dataTypeAndMaxLength.Split(new char[] { '(' });

                    ds.Tables[0].Rows[i - 1]["DATA_TYPE"] = Convert.ToString(p[0]).Trim();


                    Utils.Debugger.WriteIf("p 1 is " + p[1].ToString());

                    string maxLength = Convert.ToString(p[1]).Trim().TrimEnd(new char[] { ',' });
                    Utils.Debugger.WriteIf("maxLength is " + maxLength);
                    maxLength = Convert.ToString(maxLength).Trim().TrimEnd(new char[] { ')' });

                    Utils.Debugger.WriteIf("maxLength is " + maxLength);
                    ds.Tables[0].Rows[i - 1]["MAX_LENGTH"] = maxLength;
                }
                else
                {
                    ds.Tables[0].Rows[i - 1]["DATA_TYPE"] = Convert.ToString(Parts[1]).Trim();
                }

                continue;
            } //eof if ( Line[0] == '@
        } //eof foreach drProcText in dt.Rows

        //debug Utils.Debugger.DebugDataSet ( "from GetProcMetaData " , ref ds ) ;
        return true ;
    } //eof method 



		#endregion //eof region Methods
} //eof class TableControl
} //eof namespace DbControllers